--    Author    : ChenErHao
--    Name      : ADM.RPT_BAL_AREA.HQL
--    Functions : 
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-27  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT  OVERWRITE TABLE ADM.RPT_BAL_AREA PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG) 
-- 广西壮族自治区
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,SUM(DEP_BALANCE) AS AMOUNT                                                    -- 金额合计
   ,'A01' AS SUBJECT_SEQ                                                          -- 指标序号
   ,'D' AS FLAG                                                                   -- 存贷标识
   ,DATA_SRC_ORG                                                                  -- 上报机构
  FROM EDW.DEPB_INFO T1
  LEFT JOIN DMCODE.T_AREA_CODE T2 ON T1.DATA_DATE='#V_DATA_DATE#' AND T1.JRTJ_AREA_CODE = T2.JRTJ_AREA_CODE
 WHERE DATA_DATE='#V_DATA_DATE#' AND T2.AREA_NO_ID_1 ='450000'                   -- 地区一级编码为 广西壮族自治区
 GROUP BY DATA_SRC_ORG
  ,DATA_ORG_TYPE
 
UNION ALL
-- 450100 南宁市
-- 450200 柳州市
-- 450300 桂林市
-- 450400 梧州市
-- 450500 北海市
-- 450600 防城港市
-- 450700 钦州市
-- 450800 贵港市
-- 450900 玉林市
-- 451000 百色市
-- 451100 贺州市
-- 451200 河池市
-- 451300 来宾市
-- 451400 崇左市
SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,SUM(DEP_BALANCE) AS AMOUNT                                                    -- 金额合计
   ,CASE WHEN AREA_NO_ID_2 = '450100' THEN 'A02' 
         WHEN AREA_NO_ID_2 = '450200' THEN 'A03' 
         WHEN AREA_NO_ID_2 = '450300' THEN 'A04' 
         WHEN AREA_NO_ID_2 = '450400' THEN 'A05' 
         WHEN AREA_NO_ID_2 = '450500' THEN 'A06' 
         WHEN AREA_NO_ID_2 = '450600' THEN 'A07' 
         WHEN AREA_NO_ID_2 = '450700' THEN 'A08' 
         WHEN AREA_NO_ID_2 = '450800' THEN 'A09' 
         WHEN AREA_NO_ID_2 = '450900' THEN 'A10' 
         WHEN AREA_NO_ID_2 = '451000' THEN 'A11' 
         WHEN AREA_NO_ID_2 = '451100' THEN 'A12' 
         WHEN AREA_NO_ID_2 = '451200' THEN 'A13' 
         WHEN AREA_NO_ID_2 = '451300' THEN 'A14' 
         WHEN AREA_NO_ID_2 = '451400' THEN 'A14'
    END AS SUBJECT_SEQ                                                            -- 指标序号
   ,'D' AS FLAG                                                                   -- 存贷标识
   ,DATA_SRC_ORG                                                                  -- 上报机构
  FROM EDW.DEPB_INFO T1
  LEFT JOIN DMCODE.T_AREA_CODE T2 ON T1.DATA_DATE='#V_DATA_DATE#' AND T1.JRTJ_AREA_CODE = T2.JRTJ_AREA_CODE
 WHERE T1.DATA_DATE='#V_DATA_DATE#' 
   AND T2.AREA_NO_ID_2 IN ('450100','450200','450300','450400','450500','450600','450700','450800','450900','451000','451100','451200','451300','451400')
 GROUP BY 
    DATA_SRC_ORG
   ,T2.AREA_NO_ID_2
   ,DATA_ORG_TYPE
 
UNION ALL
-- 广西壮族自治区
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,SUM(LOAN_BALANCE         ) AS AMOUNT                                                    -- 金额合计
   ,'A01' AS SUBJECT_SEQ                                                          -- 指标序号
   ,'L' AS FLAG                                                                   -- 存贷标识
   ,DATA_SRC_ORG                                                                  -- 上报机构
  FROM EDW.LOAB_INFO T1
  LEFT JOIN DMCODE.T_AREA_CODE T2 ON T1.DATA_DATE='#V_DATA_DATE#' AND T1.JRTJ_AREA_CODE = T2.JRTJ_AREA_CODE
 WHERE DATA_DATE='#V_DATA_DATE#' AND T2.AREA_NO_ID_1 ='450000'                   -- 地区一级编码为 广西壮族自治区
 GROUP BY 
    DATA_SRC_ORG
   ,DATA_ORG_TYPE
 
UNION ALL
-- 450100 南宁市
-- 450200 柳州市
-- 450300 桂林市
-- 450400 梧州市
-- 450500 北海市
-- 450600 防城港市
-- 450700 钦州市
-- 450800 贵港市
-- 450900 玉林市
-- 451000 百色市
-- 451100 贺州市
-- 451200 河池市
-- 451300 来宾市
-- 451400 崇左市
SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,SUM(LOAN_BALANCE         ) AS AMOUNT                                                    -- 金额合计
   ,CASE WHEN T2.AREA_NO_ID_2 = '450100' THEN 'A02' 
         WHEN T2.AREA_NO_ID_2 = '450200' THEN 'A03' 
         WHEN T2.AREA_NO_ID_2 = '450300' THEN 'A04' 
         WHEN T2.AREA_NO_ID_2 = '450400' THEN 'A05' 
         WHEN T2.AREA_NO_ID_2 = '450500' THEN 'A06' 
         WHEN T2.AREA_NO_ID_2 = '450600' THEN 'A07' 
         WHEN T2.AREA_NO_ID_2 = '450700' THEN 'A08' 
         WHEN T2.AREA_NO_ID_2 = '450800' THEN 'A09' 
         WHEN T2.AREA_NO_ID_2 = '450900' THEN 'A10' 
         WHEN T2.AREA_NO_ID_2 = '451000' THEN 'A11' 
         WHEN T2.AREA_NO_ID_2 = '451100' THEN 'A12' 
         WHEN T2.AREA_NO_ID_2 = '451200' THEN 'A13' 
         WHEN T2.AREA_NO_ID_2 = '451300' THEN 'A14' 
         WHEN T2.AREA_NO_ID_2 = '451400' THEN 'A14'
    END AS SUBJECT_SEQ                                                            -- 指标序号
   ,'L' AS FLAG                                                                   -- 存贷标识
   ,DATA_SRC_ORG                                                                  -- 上报机构
  FROM EDW.LOAB_INFO T1
  LEFT JOIN DMCODE.T_AREA_CODE T2 ON T1.DATA_DATE='#V_DATA_DATE#' AND T1.JRTJ_AREA_CODE = T2.JRTJ_AREA_CODE
 WHERE T1.DATA_DATE='#V_DATA_DATE#' 
   AND T2.AREA_NO_ID_2 IN ('450100','450200','450300','450400','450500','450600','450700','450800','450900','451000','451100','451200','451300','451400')
 GROUP BY 
    DATA_SRC_ORG
   ,T2.AREA_NO_ID_2
   ,DATA_ORG_TYPE
 ;
